package beyjava.util;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * @author BEYJAVA
 * this class is used to export data to excel file
 */
public class ExcelWriter {
	private Workbook workBook;
	

	private Vector<Sheet> workBookSheets;
	private FileOutputStream fileout;
	private Map<String,CellStyle> styles;
	/**
	 * @param fileName
	 * @throws FileNotFoundException
	 */
	public ExcelWriter(String fileName) throws FileNotFoundException{
		fileout = new FileOutputStream(fileName);
		workBook = new HSSFWorkbook();
		workBookSheets = new Vector<Sheet>();
		styles =  new HashMap<String, CellStyle>();
		createStyles();
		
	}
	/**
	 * @param sheetName
	 * @return Sheet
	 */
	public Sheet createNewSheet(String sheetName){
		Sheet newSheet = this.workBook.createSheet(sheetName);
		workBookSheets.add(newSheet);
		newSheet.setDisplayGridlines(true);
        newSheet.setPrintGridlines(true);
        newSheet.setFitToPage(true);
        newSheet.setDefaultColumnWidth(20);
        newSheet.setHorizontallyCenter(true);
        newSheet.setVerticallyCenter(true);
        PrintSetup printSetup = newSheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        newSheet.setAutobreaks(true);
        printSetup.setFitHeight((short)1);
        printSetup.setFitWidth((short)1);
        
       
		return newSheet;
	}
	/**
	 * @param sheet
	 * @param rowNumber
	 * @return Row
	 */
	public Row addRowToSheet(Sheet sheet , int rowNumber){
		   Row newRow = sheet.createRow(rowNumber);
		   return newRow;
	}


/**
 * @param cell
 * @param styleName
 */
public void addStyleToCell(Cell cell, String styleName){
	cell.setCellStyle(styles.get(styleName));
}

/**
 * @param row
 * @param data
 */
public void addDataToRow(Row row ,  String[] data , String styleName){
	int i = 0;
	for(String cell:data){
		Cell newCell = row.createCell(i);
		newCell.setCellValue(cell);
		addStyleToCell(newCell, styleName);
		i++;
	}
}
/**
 * @param row
 * @param data
 * @param cellBegin
 */
public void addDataToRow(Row row , String[] data, int cellBegin, String styleName){
	int i = 0;
	for(String cell:data){
		Cell newCell = row.createCell(i+cellBegin);
		newCell.setCellValue(cell);
		addStyleToCell(newCell, styleName);
		i++;
	}
}
/**
 * @param row
 * @param data
 * @param cellBegin
 * @param length
 */
public void addDataToRow(Row row , String[] data, int cellBegin,int length,String styleName){
   
	for(int i = 0; i<length; i++){
		String cell = data[i];
		Cell newCell = row.createCell(i+cellBegin);
		newCell.setCellValue(cell);
		addStyleToCell(newCell, styleName);
		
	}
}

/**
 * 
 */
public void createStyles(){
   
    CellStyle style;
    Font headerFont = workBook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(workBook);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("headerRow", style);

    Font cellFont=workBook.createFont();
    cellFont.setColor(Font.COLOR_RED);
    style = createBorderedStyle(workBook);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(cellFont);
    styles.put("data", style);
   
}

/**
 * @param wb
 * @return
 */
public CellStyle createBorderedStyle(Workbook wb){
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THICK);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THICK);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THICK);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THICK);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}


	/**
	 * @throws IOException
	 */
	public void closeExcelWriter() throws IOException{
		workBook.write(fileout);
		fileout.close();
	}
	/**
	 * @return Workbook
	 */
	public Workbook getWorkBook() {
		return workBook;
	}
	/**
	 * @param workBook
	 */
	public void setWorkBook(Workbook workBook) {
		this.workBook = workBook;
	}

}
